
/****** Object:  Stored Procedure [dbo].mp_SeatAvailable_Delete    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_SeatAvailable_Delete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_SeatAvailable_Delete]
GO

/****** Object:  Stored Procedure [dbo].mp_SeatAvailable_SelectOne    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_SeatAvailable_SelectOne]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_SeatAvailable_SelectOne]
GO

/****** Object:  Stored Procedure [dbo].mp_SeatAvailable_GetCount    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_SeatAvailable_GetCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_SeatAvailable_GetCount]
GO

	
/****** Object:  Stored Procedure [dbo].mp_SeatAvailable_SelectAll    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_SeatAvailable_SelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_SeatAvailable_SelectAll]
GO

/****** Object:  Stored Procedure [dbo].mp_SeatAvailable_Insert    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_SeatAvailable_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_SeatAvailable_Insert]
GO

/****** Object:  Stored Procedure [dbo].mp_SeatAvailable_Update    Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_SeatAvailable_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_SeatAvailable_Update]
GO


SET QUOTED_IDENTIFIER ON 
GO


CREATE PROCEDURE [dbo].mp_SeatAvailable_Delete

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/

@ScheduleId bigint,
@DepartureDate date,
@SeatClassId int

AS

DELETE FROM [dbo].[SeatAvailable]
WHERE
	[ScheduleId] = @ScheduleId
	AND [DepartureDate] = @DepartureDate
	AND [SeatClassId] = @SeatClassId
GO


SET QUOTED_IDENTIFIER OFF 
GO



SET QUOTED_IDENTIFIER ON 
GO



CREATE PROCEDURE [dbo].mp_SeatAvailable_GetCount

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/

AS

SELECT COUNT(*) FROM [dbo].[SeatAvailable]

GO


SET QUOTED_IDENTIFIER OFF 
GO



SET QUOTED_IDENTIFIER ON 
GO


CREATE PROCEDURE [dbo].mp_SeatAvailable_SelectOne

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/

@ScheduleId bigint,
@DepartureDate date,
@SeatClassId int

AS


SELECT
		[Id],
		[ScheduleId],
		[DepartureDate],
		[SeatClassId],
		[TotalSeat],
		[CreatedDate],
		[LastUpdate]
		
FROM
		[dbo].[SeatAvailable]
		
WHERE
		[ScheduleId] = @ScheduleId
		AND [DepartureDate] = @DepartureDate
		AND [SeatClassId] = @SeatClassId

GO



SET QUOTED_IDENTIFIER OFF 
GO







SET QUOTED_IDENTIFIER ON 
GO


CREATE PROCEDURE [dbo].mp_SeatAvailable_SelectAll

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/

AS


SELECT
		[Id],
		[ScheduleId],
		[DepartureDate],
		[SeatClassId],
		[TotalSeat],
		[CreatedDate],
		[LastUpdate]
		
FROM
		[dbo].[SeatAvailable]

GO


SET QUOTED_IDENTIFIER OFF 
GO


SET QUOTED_IDENTIFIER ON 
GO



CREATE PROCEDURE [dbo].mp_SeatAvailable_Insert

-- THIS STORED PROCEDURE NEEDS TO BE MANUALLY COMPLETED
-- MULITPLE PRIMARY KEY MEMBERS OR NON-GUID/INT PRIMARY KEY

@Id bigint,
@ScheduleId bigint,
@DepartureDate date,
@SeatClassId int,
@TotalSeat int,
@CreatedDate datetime,
@LastUpdate datetime


AS

INSERT INTO 		[dbo].[SeatAvailable] 
(
					[Id],
					[ScheduleId],
					[DepartureDate],
					[SeatClassId],
					[TotalSeat],
					[CreatedDate],
					[LastUpdate]
) 

VALUES 
(
					@Id,
					@ScheduleId,
					@DepartureDate,
					@SeatClassId,
					@TotalSeat,
					@CreatedDate,
					@LastUpdate
)


GO

SET QUOTED_IDENTIFIER OFF 
GO



SET QUOTED_IDENTIFIER ON 
GO



CREATE PROCEDURE [dbo].mp_SeatAvailable_Update

/*
Author:   			HungNM
Created: 			16-08-2012
Last Modified: 		16-08-2012
*/
	
@Id bigint, 
@ScheduleId bigint, 
@DepartureDate date, 
@SeatClassId int, 
@TotalSeat int, 
@CreatedDate datetime, 
@LastUpdate datetime 


AS

UPDATE 		[dbo].[SeatAvailable] 

SET
			[TotalSeat] = @TotalSeat,
			[CreatedDate] = @CreatedDate,
			[LastUpdate] = @LastUpdate
			
WHERE
			[ScheduleId] = @ScheduleId
			AND [DepartureDate] = @DepartureDate
			AND [SeatClassId] = @SeatClassId

GO

SET QUOTED_IDENTIFIER OFF 
GO


/****** Object:  Stored Procedure [dbo].mp_SeatAvailable_SelectPage   Script Date: Thursday, August 16, 2012 ******/
if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[mp_SeatAvailable_SelectPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[mp_SeatAvailable_SelectPage]
GO



CREATE PROCEDURE [dbo].mp_SeatAvailable_SelectPage

-- Author:   			HungNM
-- Created: 			16-08-2012
-- Last Modified: 		16-08-2012

@PageNumber 			int,
@PageSize 			int

AS

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int


SET @PageLowerBound = (@PageSize * @PageNumber) - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

/*
Note: temp tables use the server default for collation not the database default
so if adding character columns be sure and specify to use the database collation like this
to avoid collation errors:

CREATE TABLE #PageIndexForUsers
(
IndexID int IDENTITY (1, 1) NOT NULL,
UserName nvarchar(50) COLLATE DATABASE_DEFAULT,
LoginName nvarchar(50) COLLATE DATABASE_DEFAULT
) 


*/

DECLARE @PageIndex TABLE 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
    ScheduleId BigInt,
    DepartureDate date,
    SeatClassId Int
)

BEGIN

INSERT INTO @PageIndex ( 
    ScheduleId,

    DepartureDate,

    SeatClassId
)

SELECT
		[ScheduleId],
		[DepartureDate],
		[SeatClassId]
		
FROM
		[dbo].[SeatAvailable]
		
-- WHERE

-- ORDER BY

END


SELECT
		t1.*
		
FROM
		[dbo].[SeatAvailable] t1

JOIN			@PageIndex t2
ON			
		t1.[ScheduleId] = t2.[ScheduleId] AND 

		t1.[DepartureDate] = t2.[DepartureDate] AND 

		t1.[SeatClassId] = t2.[SeatClassId]
		
WHERE
		t2.IndexID > @PageLowerBound 
		AND t2.IndexID < @PageUpperBound
		
ORDER BY t2.IndexID

-- DROP TABLE #PageIndex

GO


SET QUOTED_IDENTIFIER OFF 
GO







